package com.iweb.report_statistic.dao;

import com.iweb.report_statistic.model.Report;
import com.iweb.util.JdbcUtil;
import com.iweb.util.PageModel;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ReportDaoImpl implements ReportDao{

    @Override
    public List<Report> selectByPage(PageModel pageModel) {
        List<Report> reports = new ArrayList<>();
        String likeValue = pageModel.getLikeValue();
        StringBuffer sql = new StringBuffer("SELECT " +
                "    p.id AS productId, " +
                "    p.name AS productName, " +
                "    p.category AS category, " +
                "    i.quantity AS stockQuantity, " +
                "    i.warning_threshold AS warningThreshold, " +
                "    ROUND(p.price * i.quantity, 2) AS totalValue, " +
                "    IFNULL(SUM_IN.total_in, 0) AS inBoundTotal, " +
                "    IFNULL(SUM_OUT.total_out, 0) AS outBoundTotal " +
                "FROM  product p " +
                "JOIN inventory i ON p.id = i.product_id LEFT JOIN ( " +
                " SELECT  product_id, SUM(quantity) AS total_in  FROM  stock_in " +
                " GROUP BY  product_id ) AS SUM_IN ON p.id = SUM_IN.product_id " +
                "LEFT JOIN ( SELECT  product_id, SUM(quantity) AS total_out  FROM  stock_out GROUP BY product_id " +
                ") AS SUM_OUT ON p.id = SUM_OUT.product_id " +
                "WHERE  p.id LIKE ? OR  p.name LIKE ? OR p.category LIKE ? " +
                "GROUP BY p.id, p.name, p.category, i.quantity, i.warning_threshold, p.price ORDER BY p.id ");

        int recordCount = JdbcUtil.selectBySql(sql.toString(),
                "%" + likeValue + "%", "%" + likeValue + "%",
                "%" + likeValue + "%").size();

        pageModel.setRecordCount(recordCount);
        sql.append("limit ?,?");
        int startNo = pageModel.getPageSize() * (pageModel.getPageNo() - 1);

        List<Map<String, Object>> list = JdbcUtil.selectBySql(sql.toString(),
                "%" + likeValue + "%", "%" + likeValue + "%",
                "%" + likeValue + "%", startNo, pageModel.getPageSize());
        for(int i=0;i<list.size();i++){
            Map<String, Object> map = list.get(i);
            // 处理map为null的情况
            map = (map != null) ? map : new HashMap<>();
            // 字符串类型默认值处理
            String productId = (String) map.get("productId");
            productId = (productId == null) ? "未知ID" : productId;

            String productName = (String) map.get("productName");
            productName = (productName == null) ? "未知名称" : productName;

            String category = (String) map.get("category");
            category = (category == null) ? "未分类" : category;

            // 整数类型默认值处理
            Integer stockQuantity = (Integer) map.get("stockQuantity");
            stockQuantity = (stockQuantity == null) ? 0 : stockQuantity;

            Integer warningThreshold = (Integer) map.get("warningThreshold");
            warningThreshold = (warningThreshold == null) ? 0 : warningThreshold;

            //  BigDecimal类型默认值处理
            BigDecimal totalValue = (BigDecimal) map.get("totalValue");
            totalValue = (totalValue == null) ? BigDecimal.ZERO: totalValue;

            BigDecimal inBoundTotal = (BigDecimal) map.get("inBoundTotal");
            inBoundTotal = (inBoundTotal == null) ? BigDecimal.ZERO : inBoundTotal;

            BigDecimal outBoundTotal = (BigDecimal) map.get("outBoundTotal");
            outBoundTotal = (outBoundTotal == null) ? BigDecimal.ZERO : outBoundTotal;

            reports.add(new Report(productId,productName,category,stockQuantity,warningThreshold,totalValue,inBoundTotal,outBoundTotal));
        }

        return reports;
    }

    @Override
    public Report selectById(String productId) {
        return null;
    }


    //    @Override
//    public Report selectById(String productId) {
//        // 处理输入参数为null的情况
//        if (productId == null) {
//            return null; // 或抛出 IllegalArgumentException
//        }
//
//        String sql = "SELECT " +
//                "    p.id AS productId, " +
//                "    p.name AS productName, " +
//                "    p.category AS category, " +
//                "    i.quantity AS stockQuantity, " +
//                "    i.warning_threshold AS warningThreshold, " +
//                "    ROUND(p.price * i.quantity, 2) AS totalValue, " +
//                "    IFNULL(SUM_IN.total_in, 0) AS inBoundTotal, " +
//                "    IFNULL(SUM_OUT.total_out, 0) AS outBoundTotal " +
//                "FROM product p " +
//                "JOIN inventory i ON p.id = i.product_id " +
//                "LEFT JOIN ( " +
//                "    SELECT product_id, SUM(quantity) AS total_in FROM stock_in " +
//                "    GROUP BY product_id " +
//                ") AS SUM_IN ON p.id = SUM_IN.product_id " +
//                "LEFT JOIN ( " +
//                "    SELECT product_id, SUM(quantity) AS total_out FROM stock_out " +
//                "    GROUP BY product_id " +
//                ") AS SUM_OUT ON p.id = SUM_OUT.product_id " +
//                "WHERE p.id = ? " +
//                "GROUP BY p.id, p.name, p.category, i.quantity, i.warning_threshold, p.price";
//
//
//        // 确保查询结果列表不为null
//        List<Map<String, Object>> list = JdbcUtil.selectBySql(sql, productId);
//        list = (list != null) ? list : new ArrayList<>();
//
//        if (list.isEmpty()) {
//            return null;
//        }
//
//        // 确保map对象不为null
//        Map<String, Object> map = list.get(0);
//        map = (map != null) ? map : new HashMap<>();
//
//        // 处理字符串类型空值
//        String productName = (String) map.get("productName");
//        productName = (productName == null) ? "未知名称" : productName;
//
//        String category = (String) map.get("category");
//        category = (category == null) ? "未分类" : category;
//
//        // 处理整数类型空值，使用包装类过渡后设置默认值
//        Integer stockQuantityObj = (Integer) map.get("stockQuantity");
//        int stockQuantity = (stockQuantityObj != null) ? stockQuantityObj : 0;
//
//        Integer warningThresholdObj = (Integer) map.get("warningThreshold");
//        int warningThreshold = (warningThresholdObj != null) ? warningThresholdObj : 0;
//
//        // 处理BigDecimal类型空值
//        BigDecimal totalValue = (BigDecimal) map.get("totalValue");
//        totalValue = (totalValue != null) ? totalValue : BigDecimal.ZERO;
//
//        BigDecimal inBoundTotal = (BigDecimal) map.get("inBoundTotal");
//        inBoundTotal = (inBoundTotal != null) ? inBoundTotal : BigDecimal.ZERO;
//
//        BigDecimal outBoundTotal = (BigDecimal) map.get("outBoundTotal");
//        outBoundTotal = (outBoundTotal != null) ? outBoundTotal : BigDecimal.ZERO;
//
//        return new Report(productId, productName, category, stockQuantity,
//                warningThreshold, totalValue, inBoundTotal, outBoundTotal);
//    }


    public ReportDaoImpl() {
        super();
    }

    @Override
    public int hashCode() {
        return super.hashCode();
    }

    @Override
    public boolean equals(Object obj) {
        return super.equals(obj);
    }

    @Override
    protected Object clone() throws CloneNotSupportedException {
        return super.clone();
    }

    @Override
    public String toString() {
        return super.toString();
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
    }

}
